package cn.iune.erp.inventory;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import javax.persistence.NoResultException;
import javax.persistence.Query;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.justep.baas.Utils;
import com.justep.baas.data.DataUtils;
import com.justep.baas.data.Row;
import com.justep.baas.data.Table;
import com.justep.baas.data.Transform;

import cn.iune.common.base.BaseJPAImpl;



@Repository
public class StorageItemDaoJPAImpl extends BaseJPAImpl<StorageItem> implements IStorageItemDao {

//	private final Logger log = LoggerFactory.getLogger(StorageItemDaoJPAImpl.class);

	private String kSql;
	
//	@PersistenceContext 
//	private EntityManager em ;

	@Override
	public void insert(StorageItem obj) {
		em.persist(obj);
	}

	@Override
	public void delete(String fID) {
		em.remove(em.getReference(StorageItem.class, fID));
	}

	@Override
	public StorageItem update(StorageItem obj) {
		StorageItem objNew = em.merge(obj);
		return objNew;
	}

	@Override
	public StorageItem getByID(String fID) {
		StorageItem singleResult = em.find(StorageItem.class, fID);
		return singleResult;
	}

	@Override
	public List<StorageItem> getAll() {
		kSql="SELECT StorageItem FROM StorageItem StorageItem ";
		Query query = em.createQuery(kSql);
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public StorageItem getByNo(String storageID, String itemID) {
		//1、定义sql语句
		kSql="SELECT StorageItem FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.storageID = :storageID ) "
				+ " AND ( StorageItem.itemID = :itemID ) "
//				+ " AND ( StorageItem.itemProperty = :itemProperty ) "
//				+ " AND ( StorageItem.batchNo = :batchNo ) "
//				+ " AND ( StorageItem.enterpriseNo = :enterpriseNo ) "
				;

		Query query = em.createQuery(kSql);
		query.setParameter("storageID", storageID);
		query.setParameter("itemID", itemID);
//		query.setParameter("batchNo", batchNo);
//		query.setParameter("enterpriseNo", enterpriseNo);
		StorageItem singleResult = null;
		try{
			singleResult = (StorageItem) query.getSingleResult();
		}catch(NoResultException e){
			//捕获没有结果的异常
			//避免抛出异常而中断对前台的相应。
		}finally{
		}
		return singleResult;
	}
	

	/* (non-Javadoc)
	 * @see com.iune.erp.inventory.IStorageItemService#queryJDBC(com.alibaba.fastjson.JSONObject)
	 */
	
	@Override
	public JSONObject create(JSONObject data) {
		//缺省列
		String defColstr = Utils.getDefCols(StorageItem.class);
		data.put("defColstr", defColstr);
		return Utils.newData(data);

	}

	//按库位显示
	@Override
	public JSONObject query(JSONObject data) throws SQLException {
//		System.out.println("data-->"+data);
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
//		System.out.println("columns-->"+columns);
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fMasterID = data.getString("fMasterID");
		System.out.println("fMasterID-->"+fMasterID);
//		System.out.println("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		String format = "SELECT %s FROM %s %s %s ";
		
		String select = "StorageItem.*"
				
				+ ",FileItem.itemNo"
				+ ",FileItem.itemName"
				+ ",FileItem.itemProperty"
//				+ ",CONCAT(FileItem.itemName,IFNULL(ItemProperty.propertyLabel,'')) as itemDescribe"
//				+ ",CONCAT(FileItem.itemName,'【',IFNULL(FileItem.itemProperty,''),'】') as itemDescribe"
				+ ",if(FileItem.itemProperty is null,FileItem.itemName,CONCAT(FileItem.itemName,'【',FileItem.itemProperty,'】')) as itemDescribe"
				+ ",FileItem.fParent"
				+ ",FileItem.configType"
				+ ",FileItem.unitName"

				+ ",FileItem.qualityType"
				+ ",FileItem.qualityTypeLabel"
				+ ",FileItem.qualityPeriod"
				+ ",FileItem.validDays"
				+ ",FileItem.checkoutAgainDays"
				+ ",FileItem.storeNo"
				+ ",FileItem.barcode"
				+ ",FileItem.batchNo"
				
				+ ",FileItem.process"
				+ ",FileItem.processLabel"
				
				+ ",FileItem.forSale"
				+ ",FileItem.forUsed"
				
				+ ",FileItem.taxRate"
				
				+ ",FileItem.purchaseTaxType"
				+ ",FileItem.purchasePrice"
				+ ",FileItem.saleTaxType"
				+ ",FileItem.salePrice"
				+ ",FileItem.processTaxType"
				+ ",FileItem.processPrice"
				
				+ ",FileItem.itemWeight"
				+ ",FileItem.itemSize"
				+ ",FileItem.orderWeight"
				+ ",FileItem.orderSize"
				
				+ ",FileItem.images"

				+ ",FileItem.materialStandardPer"
				+ ",FileItem.workingStandardPer"
				+ ",FileItem.manufactStandardPer"
				+ ",FileItem.processStandardPer"
				
				+ ",FileItem.costingType"
				
				+ ",IF(StorageItem.itemQuantityL=0,0,CONVERT(StorageItem.costingAmount/StorageItem.itemQuantityL,decimal(38,6))) as costingAmountPer"
				
				/*
				//截取第二个‘/’之前的所有字符，然后再截取第一个‘/’之后的所有字符（从右向左数）
				+ ",substring_index(substring_index(FileStorage.storageFullNo,'/',2),'/',-1) as storageNo_tmp"
				//截取第二个‘/’之前的所有字符，然后再截取第一个‘/’之后的所有字符
				+ ",substring_index(substring_index(FileStorage.storageFullName,'/',2),'/',-1) as storageName_tmp"
				*/
				
				/*
				+ ",substring_index(substring_index(FileFloor.storageFullID,'/',2),'/',-1) as storageID_tmp"
				+ ",FileStorage.storageNo"
				+ ",FileStorage.storageName"
				
				//组合工厂名称和仓库全路径名称
				+ ",FileFloor.storageFullName as floorNo"
				
				+ ",FileFloor.storageType"
				+ ",FileFloor.storageTypeLabel"
				
				+ ",FileFloor.factoryID"
				+ ",FileFactory.factoryNo"
				+ ",FileFactory.factoryName"
				*/
				
				//库存金额计算（保留两位小数）
//				+ ",FileItem.costingAmountPer*StorageItem.itemQuantityL as costingAmount"
				//整数部分超过三位的时候以逗号分割，并且返回的结果是string类型的。
//				+ ",FORMAT(FileItem.costingAmountPer*StorageItem.itemQuantityL,2) as costingAmount"
				//直接截取不四舍五入，还是有问题。
//				+ ",TRUNCATE(FileItem.costingAmountPer*StorageItem.itemQuantityL,2) as costingAmount"


				//+ ",substring_index(substring_index(FileFloor.storageFullID,'/',2),'/',-1) as rootID"
				+ ",FileStorage.storageNo"
				+ ",FileStorage.storageName"

				+ ",FileStorage.storageFullName as floorNo"

				+ ",FileStorage.storageType"
				+ ",FileStorage.storageTypeLabel"

				+ ",FileStorage.factoryID"
				+ ",FileStorage.factoryNo"
				+ ",FileStorage.factoryName"
								
				;
		
//		String from = "StorageItem StorageItem";
		String from = "StorageItem StorageItem LEFT JOIN FileItem FileItem ON StorageItem.itemID = FileItem.fID "
				+ " LEFT JOIN FileStorage FileStorage ON StorageItem.storageID = FileStorage.fID"//库位表
//				+ " LEFT JOIN FileStorage FileStorage ON substring_index(substring_index(FileFloor.storageFullID,'/',2),'/',-1) = FileStorage.fID"//仓库表

				;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件（临时生成的列不能作为过滤条件查询的目标，只能是数据库中真实存在的）
			filters.add("FileItem.itemNo LIKE ?  OR FileItem.itemName LIKE ?  OR FileStorage.storageFullName LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 3; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("StorageItem.fID='"+id+"'");
		}
		//处理fMasterID
		if (Utils.isNotEmptyString(fMasterID)) {
			filters.add("StorageItem.itemID='"+fMasterID+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters"); // 列定义
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
//				System.out.println("jsonFilter.getString(\"filter\")-->"+jsonFilter.getString("filter"));
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
		columns = null;
		//没有下面的三行代码，默认也是可以查询出同样的结果
		if(columns==null){
			columns = Utils.entityToDefColJSON(StorageItem.class);
		}
		
//		System.out.println("columns-->"+columns);
//		
//		System.out.println("filters-->"+filters);
//		System.out.println("sqlParams-->"+sqlParams);
		
		
		String where = "";
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		System.out.println("sql-->"+sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
//		System.out.println("total-->"+total);
		//SELECT ShopCart.fID FROM ShopCart ShopCart    LIMIT 0,-1//为-1时报错。
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);

		table.setTotal(Integer.parseInt(total.toString()));
		JSONObject json = Transform.tableToJson(table);
		System.out.println("table-->"+json);
		//////////////////////////////////自定义聚合数据/////////////////////////////////
		//////////////////////////////////////////////////////////////////////////////
		JSONObject aggregateColumns = data.getJSONObject("aggregateColumns"); // 聚合列定义
//		System.out.println("aggregateColumns-->"+aggregateColumns);
		if(aggregateColumns!=null){
			String aggregateSelect = "";
			
			Set<String> keys = aggregateColumns.keySet();
			int index = 0;
			for(String key:keys){
				if(index==0){
					aggregateSelect = aggregateSelect + "SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}else{
					aggregateSelect = aggregateSelect + ",SUM(" + key + ") as " + aggregateColumns.getJSONObject(key).getString("name");
				}
				
				index++;
			}
			
//			aggregateSelect = "SUM(StorageItem.itemQuantityL) as sumQuantityL,SUM(CONVERT(FileItem.costingAmountPer*StorageItem.itemQuantityL,decimal(38,2))) as sumCostingAmount,SUM(StorageItem.pieceQuantityL) as sumPieceQuantityL";
			
			String aggregateSql = String.format(format, aggregateSelect, from, where, "");
//			System.out.println("aggregateSql-->"+aggregateSql);
			Table aggregateTable = DataUtils.queryData(conn, aggregateSql, sqlParams, aggregateColumns, 0, table.getTotal());
//			Row row = aggregateTable.getRows().get(0);
//			JSONObject aggregateValues = Transform.rowToJson(aggregateTable, row);
			JSONObject jo = Transform.tableToJson(aggregateTable);
			JSONArray jarray = jo.getJSONArray("rows");
			JSONObject aggregateValues = new JSONObject();
			if(jarray.size()>0){
				aggregateValues = jarray.getJSONObject(0);
			}
//			System.out.println("aggregateValues-->"+aggregateValues);
			//jb-->{"userdata":{"recordState":"none"},"costingAmount":{"value":"599517613.43"},"costingAmountPer":{"value":"59957.893727"}}
			json.put("aggregateValues", aggregateValues);
			
		}
		//////////////////////////////////////////////////////////////////////////////////
		//////////////////////////////////////////////////////////////////////////////////

		//关闭连接
		conn.close();
		
		return json;
	}
	
	@Override
	public JSONObject querySimple(JSONObject data) throws SQLException {
//		System.out.println("data-->"+data);
		// 获取参数
		JSONObject columns = data.getJSONObject("columns"); // 列定义
//		System.out.println("columns-->"+columns);
		String orderBy = data.getString("orderBy"); // 排序
		Integer limit = data.getInteger("limit"); // 分页查询的行数
		Integer offset = data.getInteger("offset"); // 分页查询的行偏移
		String search = data.getString("search"); // 检索关键字
		String filter = data.getString("filter"); // 过滤条件
		String id = data.getString("id");
		String fParent = data.getString("fParent");
//		System.out.println("limit-->"+limit);
//		System.out.println("offset-->"+offset);
//		log.info("filters-->"+filters);
//		log.info("sqlParams-->"+params);
		/*
		limit-->15
		offset-->0
		filters-->[unitCode LIKE ? OR unitName LIKE ? OR unitType LIKE ?]
		sqlParams-->[%3%, %3%, %3%]
		*/		
		
		String format = "SELECT %s FROM %s %s %s ";
//		String format = "SELECT ShopCart FROM ShopCart ShopCart %s %s ";
		
		String select = "StorageItem.*"
				
				+ ",FileItem.itemNo"
				+ ",FileItem.itemName"
				+ ",FileItem.itemProperty"
//				+ ",CONCAT(FileItem.itemName,IFNULL(ItemProperty.propertyLabel,'')) as itemDescribe"
//				+ ",CONCAT(FileItem.itemName,'【',IFNULL(FileItem.itemProperty,''),'】') as itemDescribe"
				+ ",if(FileItem.itemProperty is null,FileItem.itemName,CONCAT(FileItem.itemName,'【',FileItem.itemProperty,'】')) as itemDescribe"
				+ ",FileItem.fParent"
				+ ",FileItem.configType"
				+ ",FileItem.unitName"

				+ ",FileItem.qualityType"
				+ ",FileItem.qualityTypeLabel"
				+ ",FileItem.qualityPeriod"
				+ ",FileItem.validDays"
				+ ",FileItem.checkoutAgainDays"
				+ ",FileItem.storeNo"
				+ ",FileItem.barcode"
				+ ",FileItem.batchNo"
				
				+ ",FileItem.process"
				+ ",FileItem.processLabel"
				
				+ ",FileItem.forSale"
				+ ",FileItem.forUsed"
				
				+ ",FileItem.taxType"
				+ ",FileItem.taxTypeLabel"
				+ ",FileItem.taxRate"
				+ ",FileItem.itemPrice"
				
				+ ",FileItem.saxType"
				+ ",FileItem.saxTypeLabel"
				+ ",FileItem.saxRate"
				+ ",FileItem.salePrice"
				
				+ ",FileItem.itemWeight"
				+ ",FileItem.itemSize"
				+ ",FileItem.orderWeight"
				+ ",FileItem.orderSize"
				
				+ ",FileItem.imageurl"
				+ ",FileItem.images"
				+ ",FileItem.itemClassID"

				+ ",FileItem.costingAmountPer"
				+ ",FileItem.costingStandardPer"
				+ ",FileItem.costingType"

				+ ",CONVERT(FileItem.costingAmountPer*StorageItem.itemQuantityL,decimal(38,2)) as costingAmount"
				/*
				//截取第二个‘/’之前的所有字符，然后再截取第一个‘/’之后的所有字符（从右向左数）
				+ ",substring_index(substring_index(FileStorage.storageFullNo,'/',2),'/',-1) as storageNo_tmp"
				//截取第二个‘/’之前的所有字符，然后再截取第一个‘/’之后的所有字符
				+ ",substring_index(substring_index(FileStorage.storageFullName,'/',2),'/',-1) as storageName_tmp"
				*/
				
				/*
				+ ",substring_index(substring_index(FileFloor.storageFullID,'/',2),'/',-1) as storageID_tmp"
				+ ",FileStorage.storageNo"
				+ ",FileStorage.storageName"
				
				//组合工厂名称和仓库全路径名称
//				+ ",CONCAT(FileFactory.factoryName,FileStorage.storageFullName) as floorNo"
				+ ",FileFloor.storageFullName as floorNo"
				
				+ ",FileFloor.storageType"
				+ ",FileFloor.storageTypeLabel"
				
				+ ",FileFloor.factoryID"
				+ ",FileFactory.factoryNo"
				+ ",FileFactory.factoryName"
				*/
				
				//库存金额计算（保留两位小数）
//				+ ",FileItem.costingAmountPer*StorageItem.itemQuantityL as costingAmount"
				//整数部分超过三位的时候以逗号分割，并且返回的结果是string类型的。
//				+ ",FORMAT(FileItem.costingAmountPer*StorageItem.itemQuantityL,2) as costingAmount"
				//直接截取不四舍五入，还是有问题。
//				+ ",TRUNCATE(FileItem.costingAmountPer*StorageItem.itemQuantityL,2) as costingAmount"



				//+ ",substring_index(substring_index(FileFloor.storageFullID,'/',2),'/',-1) as rootID"
				+ ",FileStorage.storageNo"
				+ ",FileStorage.storageName"

				+ ",FileStorage.storageFullName as floorNo"

				+ ",FileStorage.storageType"
				+ ",FileStorage.storageTypeLabel"

				+ ",FileStorage.factoryID"
				+ ",FileStorage.factoryNo"
				+ ",FileStorage.factoryName"
								
								;
//		String from = "StorageItem StorageItem";
		String from = "StorageItem StorageItem LEFT JOIN FileItem FileItem ON StorageItem.itemID = FileItem.fID "
				+ " LEFT JOIN FileStorage FileStorage ON StorageItem.storageID = FileStorage.fID"//库位表
//				+ " LEFT JOIN FileStorage FileStorage ON substring_index(substring_index(FileFloor.storageFullID,'/',2),'/',-1) = FileStorage.fID"//仓库表

								;

		
		// 存放SQL中的参数值
		List<Object> sqlParams = new ArrayList<Object>();
		// 存放SQL中的过滤条件
		List<String> filters = new ArrayList<String>();
		
		if (Utils.isNotEmptyString(search)) {
			// 增加过滤条件（临时生成的列不能作为过滤条件查询的目标，只能是数据库中真实存在的）
			filters.add("FileItem.barcode LIKE ?  OR FileItem.itemNo LIKE ?  OR FileItem.itemName LIKE ?   OR FileFactory.factoryName LIKE ?  OR FileStorage.storageFullName LIKE ? ");
			// 检索关键字中如果没有%，则前后自动加%
			search = (search.indexOf("%") != -1) ? search : "%" + search + "%";
			// sqlParams的参数个数和顺序必须与过滤条件的?相匹配 
			for (int i = 0; i < 5; i++) {
				sqlParams.add(search);
			}
		}
		
		//处理过滤条件
		if (Utils.isNotEmptyString(filter)) {
			filters.add(filter);
		}
		//处理id
		if (Utils.isNotEmptyString(id)) {
			filters.add("StorageItem.fID='"+id+"'");
		}
		//处理fParent
		if (Utils.isNotEmptyString(fParent)) {
			filters.add("FileItem.fParent='"+fParent+"'");
		}
		
		//处理条件过滤
		JSONArray filterList = data.getJSONArray("filters"); // 列定义
		if(filterList!=null && filterList.size()>0){
			for(int i=0;i<filterList.size();i++){
				JSONObject jsonFilter = filterList.getJSONObject(i);
//				System.out.println("jsonFilter.getString(\"filter\")-->"+jsonFilter.getString("filter"));
				filters.add(jsonFilter.getString("filter"));
			}
		}
		
		
		System.out.println("columns-->"+columns);
		columns = null;
		//没有下面的三行代码，默认也是可以查询出同样的结果
		if(columns==null){
			columns = Utils.entityToDefColJSON(StorageItem.class);
		}
		
//		System.out.println("columns-->"+columns);
//		
		System.out.println("filters-->"+filters);
//		System.out.println("sqlParams-->"+sqlParams);
		
		
		String where = "";
		
		if(filters != null && filters.size() > 0){
			if(where.indexOf("WHERE")== -1){
				//不包含WHERE关键字
				where = "WHERE " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}else{
				//已包含
				where = where + " AND " + DataUtils.arrayJoin(filters.toArray(), "(%s)", " AND ");
			}
		}
		
		//orderBy = "sortNo ASC";
//		String where = (filters != null && filters.size() > 0) ? " WHERE " + CommonService.arrayJoin(filters.toArray(), "(%s)", " AND ") : "";
		orderBy = Utils.isNotEmptyString(orderBy) ? " ORDER BY " + orderBy : "";
		
		String sql = String.format(format, select, from, where, orderBy);
		System.out.println("sql-->"+sql);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		String sqlTotal = String.format(format, "COUNT(*)", from, where, "");
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		if(limit==-1){
			limit = Integer.parseInt(total.toString());
		}
		
//		Table table = DataUtils.queryData(conn, tableName, columns, filters, orderBy, params, offset, limit);
		Table table = DataUtils.queryData(conn, sql, sqlParams, columns, offset, limit);

		table.setTotal(Integer.parseInt(total.toString()));
		
		JSONArray arrayItem = new JSONArray();

		List<Row> list = table.getRows();
		for(Row row : list){
			JSONObject javaObject= Transform.rowToJavaObject(table, row);
			StorageItem obj = JSON.toJavaObject(javaObject, StorageItem.class);
			
			arrayItem.add(obj);
		}
		
		JSONObject json = new JSONObject();
		
		json.put("data", arrayItem);
		json.put("total", Integer.parseInt(total.toString()));
		json.put("code", 0);
		json.put("msg", "success");

		//关闭连接
		conn.close();
		
		return json;
	}

	

	//门店商品列表
	@Override
	public List<StorageItem> getList(String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageItem FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.enterpriseNo = :enterpriseNo ) "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<StorageItem> getList(String storehouseID, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageItem FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.storehouseID = :storehouseID ) "
				+ " AND ( StorageItem.enterpriseNo = :enterpriseNo ) "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("storehouseID", storehouseID);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}


	@Override
	public List<StorageItem> getByStorageID(String storageID) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageItem FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.storageID = :storageID ) "
//				+ " AND ( StorageItem.enterpriseNo = :enterpriseNo ) "
				;
//		log.info("kSql-->"+kSql);
		Query query = em.createQuery(kSql);
		query.setParameter("storageID", storageID);
//		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<StorageItem> getByItemID(String itemID, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageItem FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.itemID = :itemID ) "
				+ " AND ( StorageItem.enterpriseNo = :enterpriseNo ) "
				;
//		log.info("kSql-->"+kSql);
		Query query = em.createQuery(kSql);
		query.setParameter("itemID", itemID);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}


	//指定商品的库位列表
	@Override
	public List<StorageItem> getStorageList(String storehouseID, String itemID) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageItem FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.storehouseID = :storehouseID ) "
				+ " AND ( StorageItem.itemID = :itemID ) "
				;
		Query query = em.createQuery(kSql);
		query.setParameter("storehouseID", storehouseID);
		query.setParameter("itemID", itemID);
		
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}
	

	/*
	@Override
	public List<StorageItem> getStorageByStorageNo(String storageNo, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageItem.* FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.storageNo = :storageNo ) "
				+ " AND ( StorageItem.floorNo = '/'+:storageNo ) "
//				+ " AND ( StorageItem.factoryNo = :factoryNo ) "
				+ " AND ( StorageItem.enterpriseNo = :enterpriseNo ) "
				;
//		log.info("kSql-->"+kSql);
		Query query = em.createQuery(kSql);
		query.setParameter("storageNo", storageNo);
//		query.setParameter("itemNo", itemNo);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}

	@Override
	public List<StorageItem> getFloorByStorageNo(String storageNo, String enterpriseNo) {
		// TODO Auto-generated method stub
		kSql="SELECT StorageItem.* FROM StorageItem StorageItem "
				+ " WHERE ( StorageItem.storageNo = :storageNo ) "
				+ " AND ( StorageItem.floorNo <> '/'+:storageNo ) "
//				+ " AND ( StorageItem.factoryNo = :factoryNo ) "
				+ " AND ( StorageItem.enterpriseNo = :enterpriseNo ) "
				;
//		log.info("kSql-->"+kSql);
		Query query = em.createQuery(kSql);
		query.setParameter("storageNo", storageNo);
//		query.setParameter("itemNo", itemNo);
		query.setParameter("enterpriseNo", enterpriseNo);
		
		@SuppressWarnings("unchecked")
		List<StorageItem> resultList = query.getResultList();
		return resultList;
	}
	*/

	//库存金额（不再使用）
	@Override
	public BigDecimal getCostingAmount(List<Object> sqlParams) throws SQLException {
		// TODO Auto-generated method stub
		
		String format = "SELECT %s FROM %s %s %s ";
		String select = "SUM(CONVERT(FileItem.costingAmountPer*StorageItem.itemQuantityL,decimal(38,2))) as costingAmount "
				//+ " WHERE StorageItem.enterpriseNo = ? "
				;
		String from = "StorageItem StorageItem LEFT JOIN FileItem FileItem ON StorageItem.itemID = FileItem.fID "
				;
		String where = "WHERE StorageItem.enterpriseNo = ? ";
		
		String sqlTotal = String.format(format, select, from, where, "");
		System.out.print("sqlTotal-->"+sqlTotal);
		
//		Connection conn = DataUtils.getConnection(DataUtils.DATASOURCE_1);
		Connection conn = super.getConnection();
		Object total = DataUtils.getValueBySQL(conn, sqlTotal, sqlParams);
		//Integer.parseInt(total.toString());
		//double ca = Double.parseDouble();
		//构造以字符串内容为值的BigDecimal类型的变量
		BigDecimal costingAmount = new BigDecimal(total.toString());
		//设置小数位数，第一个变量是小数位数，第二个变量是取舍方法(四舍五入) 
		costingAmount.setScale(2, BigDecimal.ROUND_HALF_UP);
		
		return costingAmount;
	}

	//库存数量（不再使用）
	@Override
	public BigDecimal getQuantityL(List<Object> params) {
		// TODO Auto-generated method stub
		String sql = "SELECT SUM(StorageItem.itemQuantityL) "
				+ " FROM StorageItem StorageItem "
				+ " WHERE StorageItem.enterpriseNo = :enterpriseNo "
				;
		Query query = em.createQuery(sql);
		query.setParameter("enterpriseNo", params.get(0));
		
		BigDecimal singleResult =  (BigDecimal) query.getSingleResult();
		return singleResult;
	}

	
}
